package org.jeecg.modules.online.config.service.impl;

import org.jeecg.modules.online.config.exception.DBException;
import org.jeecg.modules.online.config.service.DbTableHandleI;
import org.jeecg.modules.online.config.util.ColumnMeta;


/**
 * postgres的表工具类
 */
public class DbTablePostgresHandleImpl implements DbTableHandleI {

	
	@Override
	public String getAddColumnSql(ColumnMeta columnMeta) {
		return " ADD COLUMN "+getAddFieldDesc(columnMeta)+";";
	}

	
	@Override
	public String getReNameFieldName(ColumnMeta columnMeta) {
		return " RENAME  COLUMN  "+columnMeta.getOldColumnName() +" to "+columnMeta.getColumnName()+";";
	}

	
	@Override
	public String getUpdateColumnSql(ColumnMeta cgformcolumnMeta,ColumnMeta datacolumnMeta)throws DBException {
		return getUpdateFieldDesc(cgformcolumnMeta,datacolumnMeta);
	}

	
	@Override
	public String getSpecialHandle(ColumnMeta cgformcolumnMeta,
			ColumnMeta datacolumnMeta) {
		return "  ALTER  COLUMN   "+getUpdateFieldDefault(cgformcolumnMeta,datacolumnMeta)+";";
	}
	
	
	@Override
	public String getMatchClassTypeByDataType(String dataType,int digits) {
		String result ="";
		if (dataType.equalsIgnoreCase("varchar")) {
			result="string";
		} else if(dataType.equalsIgnoreCase("double")){
			result="double";
		}else if (dataType.contains("int")) {
			result="int";
		}else if (dataType.equalsIgnoreCase("Date")) {
			result="date";
		}else if (dataType.equalsIgnoreCase("timestamp")) {
			result="date";
		}else if (dataType.equalsIgnoreCase("bytea")) {
			result="blob";
		}else if (dataType.equalsIgnoreCase("text")) {
			result="text";
		}else if (dataType.equalsIgnoreCase("decimal")) {
			result="bigdecimal";
		}else if (dataType.equalsIgnoreCase("numeric")) {
			//double和decimal都会返回numeric，先暂时返回bigdecimal
			result="bigdecimal";
		}
		return result;
	}

	
	@Override
	public String dropTableSQL(String tableName) {
		return " DROP TABLE  "+tableName+" ;";
	}

	
	@Override
	public String getDropColumnSql(String fieldName) {
		 return " DROP COLUMN "+fieldName+";";
	}

	/**
	 * 判断是不是数字之间的转换
	 * @return
	 */
	private boolean isBetweenNumber(String oldType, String newType){
		oldType = oldType==null?"":oldType.toLowerCase();
		newType = newType==null?"":newType.toLowerCase();
		String numberTypes = "int,double,bigdecimal";
		return numberTypes.indexOf(oldType)>=0 && numberTypes.indexOf(newType)>=0;
	}

	/**
	 * 获取语句 先删 后增
	 * @param cgfromcolumnMeta
	 * @param datacolumnMeta
	 * @return
	 */
	private String getDropAndAddSql(ColumnMeta cgfromcolumnMeta,ColumnMeta datacolumnMeta){
		String dropSql = getDropColumnSql(datacolumnMeta.getColumnName());
		String tableName = cgfromcolumnMeta.getTableName();
		String alterSql = String.format("alter table %s", tableName);
		String addSql = alterSql+getAddColumnSql(cgfromcolumnMeta);
		return dropSql+addSql;
	}

	/**
	 * 修改字段的时候 获取设置、取消非空约束的sql语句
	 * @return
	 */
	private String getUpdateNullableSql(ColumnMeta cgfromcolumnMeta,ColumnMeta datacolumnMeta){
		String nullable = cgfromcolumnMeta.getIsNullable();
		String oldNullable = datacolumnMeta.getIsNullable();
		nullable = nullable==null?"Y":nullable;
		oldNullable = oldNullable==null?"Y":oldNullable;
		if(!nullable.equals(oldNullable)){
			//如果非空约束发生改变 判断当前是什么
			String tableName = cgfromcolumnMeta.getTableName();
			String columnName = cgfromcolumnMeta.getColumnName();
			String alterSql = "ALTER table %s ALTER COLUMN %s %s not null;";
			if("Y".equals(nullable)){
				// 当前字段不允许为空
				return String.format(alterSql, tableName, columnName, "drop");
			}else if("N".equals(nullable)){
				// 当前字段可以为空
				return String.format(alterSql, tableName, columnName, "set");
			}
		}
		return "";
	}

	private String getUpdateFieldDesc(ColumnMeta cgfromcolumnMeta,ColumnMeta datacolumnMeta) throws DBException {
		//update-begin-author:taoyan date:20211227 for: JTC-243【Online表单】Postgres修改数据库类型为integer后普通同步失败
		String result ="  ALTER  COLUMN   ";
		//TODO 对于非空情况 ，需要特殊增加约束方法，默认是空
		if(cgfromcolumnMeta.getColunmType().equalsIgnoreCase("string")){
			result += cgfromcolumnMeta.getColumnName()+"  type character varying("+cgfromcolumnMeta.getColumnSize()+")"+" ";
		}else if(cgfromcolumnMeta.getColunmType().equalsIgnoreCase("date")){
			result += cgfromcolumnMeta.getColumnName()+"  type timestamp"+" ";
		}else if(cgfromcolumnMeta.getColunmType().equalsIgnoreCase("int")){
			//postpres数据库整形没有长度概念
			if(isBetweenNumber(cgfromcolumnMeta.getColunmType(), datacolumnMeta.getColunmType())){
				result += cgfromcolumnMeta.getColumnName()+" type int4";
			}else{
				result = getDropAndAddSql(cgfromcolumnMeta, datacolumnMeta);
			}
		}else if(cgfromcolumnMeta.getColunmType().equalsIgnoreCase("double")){
			if(isBetweenNumber(cgfromcolumnMeta.getColunmType(), datacolumnMeta.getColunmType())){
				result += cgfromcolumnMeta.getColumnName()+" type  numeric("+cgfromcolumnMeta.getColumnSize()+","+cgfromcolumnMeta.getDecimalDigits()+")"+" ";
			}else{
				result = getDropAndAddSql(cgfromcolumnMeta, datacolumnMeta);
			}
		}else if(cgfromcolumnMeta.getColunmType().equalsIgnoreCase("BigDecimal")){
			if(isBetweenNumber(cgfromcolumnMeta.getColunmType(), datacolumnMeta.getColunmType())){
				result += cgfromcolumnMeta.getColumnName()+" type  decimal("+cgfromcolumnMeta.getColumnSize()+","+cgfromcolumnMeta.getDecimalDigits()+")"+" ";
			}else{
				result = getDropAndAddSql(cgfromcolumnMeta, datacolumnMeta);
			}
		}else if(cgfromcolumnMeta.getColunmType().equalsIgnoreCase("text")){
            //update-begin--Author:scott----  Date:20190924 ----for：Postgresql数据库,字段为Text类型添加字段语法不对(不需要精度)---
		    result += cgfromcolumnMeta.getColumnName()+" text ";
            //update-end--Author:scott----  Date:20190924 ----for：Postgresql数据库,字段为Text类型添加字段语法不对(不需要精度)----
		}else if(cgfromcolumnMeta.getColunmType().equalsIgnoreCase("blob")){
//			bytea类型不可修改，修改会报错
			throw new DBException("blob类型不可修改");
		}
		if(!result.endsWith(";")){
			result+=";";
		}
		// 非空处理
		String nullableSql = getUpdateNullableSql(cgfromcolumnMeta, datacolumnMeta);
		result+=nullableSql;
		//update-end-author:taoyan date:20211227 for: JTC-243【Online表单】Postgres修改数据库类型为integer后普通同步失败
		return result;
	}

	private String getUpdateFieldDefault(ColumnMeta cgfromcolumnMeta,ColumnMeta datacolumnMeta) {
		String result ="";

		if(!cgfromcolumnMeta.equalsDefault(datacolumnMeta)){
			if(cgfromcolumnMeta.getColunmType().equalsIgnoreCase("string")){
				result = cgfromcolumnMeta.getColumnName();
				result += (org.apache.commons.lang.StringUtil.isNotEmpty(cgfromcolumnMeta.getFieldDefault())?" SET DEFAULT "+cgfromcolumnMeta.getFieldDefault():" DROP DEFAULT");
			}else if(cgfromcolumnMeta.getColunmType().equalsIgnoreCase("date")){
				result = cgfromcolumnMeta.getColumnName();
				result += (org.apache.commons.lang.StringUtil.isNotEmpty(cgfromcolumnMeta.getFieldDefault())?" SET DEFAULT "+cgfromcolumnMeta.getFieldDefault():" DROP DEFAULT");
			}else if(cgfromcolumnMeta.getColunmType().equalsIgnoreCase("int")){
				result = cgfromcolumnMeta.getColumnName();
				result += (org.apache.commons.lang.StringUtil.isNotEmpty(cgfromcolumnMeta.getFieldDefault())?" SET DEFAULT "+cgfromcolumnMeta.getFieldDefault():" DROP DEFAULT");
			}else if(cgfromcolumnMeta.getColunmType().equalsIgnoreCase("double")){
				result = cgfromcolumnMeta.getColumnName();
				result += (org.apache.commons.lang.StringUtil.isNotEmpty(cgfromcolumnMeta.getFieldDefault())?" SET DEFAULT "+cgfromcolumnMeta.getFieldDefault():" DROP DEFAULT");
			}else if(cgfromcolumnMeta.getColunmType().equalsIgnoreCase("bigdecimal")){
				result = cgfromcolumnMeta.getColumnName();
				result += (org.apache.commons.lang.StringUtil.isNotEmpty(cgfromcolumnMeta.getFieldDefault())?" SET DEFAULT "+cgfromcolumnMeta.getFieldDefault():" DROP DEFAULT");
			}else if(cgfromcolumnMeta.getColunmType().equalsIgnoreCase("text")){
				result = cgfromcolumnMeta.getColumnName();
				result += (org.apache.commons.lang.StringUtil.isNotEmpty(cgfromcolumnMeta.getFieldDefault())?" SET DEFAULT "+cgfromcolumnMeta.getFieldDefault():" DROP DEFAULT");
			}

		}
		return result;
	}
	
	
	private String getAddFieldDesc(ColumnMeta cgfromcolumnMeta) {
		String result ="";
		if(cgfromcolumnMeta.getColunmType().equalsIgnoreCase("string")){
			result = cgfromcolumnMeta.getColumnName()+" character varying("+cgfromcolumnMeta.getColumnSize()+")"+" ";
		}else if(cgfromcolumnMeta.getColunmType().equalsIgnoreCase("date")){
			result = cgfromcolumnMeta.getColumnName()+" timestamp"+" ";
		}else if(cgfromcolumnMeta.getColunmType().equalsIgnoreCase("int")){
			result = cgfromcolumnMeta.getColumnName()+" int4";
		}else if(cgfromcolumnMeta.getColunmType().equalsIgnoreCase("double")){
			result = cgfromcolumnMeta.getColumnName()+" numeric("+cgfromcolumnMeta.getColumnSize()+","+cgfromcolumnMeta.getDecimalDigits()+")"+" ";
		}else if(cgfromcolumnMeta.getColunmType().equalsIgnoreCase("bigdecimal")){
			result = cgfromcolumnMeta.getColumnName()+" decimal("+cgfromcolumnMeta.getColumnSize()+","+cgfromcolumnMeta.getDecimalDigits()+")"+" ";
		}else if(cgfromcolumnMeta.getColunmType().equalsIgnoreCase("blob")){
			//update-begin-author:taoyan date:20211227 for:JTC-254【Online表单】Postgres，新增Blob字段，普通同步失败
			// postgre 添加bytea列：不需要size
			result = cgfromcolumnMeta.getColumnName()+" bytea ";
			//update-end-author:taoyan date:20211227 for:JTC-254【Online表单】Postgres，新增Blob字段，普通同步失败
		}else if(cgfromcolumnMeta.getColunmType().equalsIgnoreCase("text")){
            //update-begin--Author:scott----  Date:20190924 ----for：Postgresql数据库,字段为Text类型添加字段语法不对(不需要精度)---
		    result = cgfromcolumnMeta.getColumnName()+" text ";
            //update-end--Author:scott----  Date:20190924 ----for：Postgresql数据库,字段为Text类型添加字段语法不对(不需要精度)---
		}
		result += (org.apache.commons.lang.StringUtil.isNotEmpty(cgfromcolumnMeta.getFieldDefault())?" DEFAULT "+cgfromcolumnMeta.getFieldDefault():" ");
		// update-begin-author:taoyan date:20220105 for: 字段非空约束 处理
		if("N".equals(cgfromcolumnMeta.getIsNullable())){
			result+=" NOT NULL ";
		}
		// update-end-author:taoyan date:20220105 for: 字段非空约束 处理
		return result;
	}
	
	private String getRenameFieldDesc(ColumnMeta cgfromcolumnMeta) {
		String result ="";
		if(cgfromcolumnMeta.getColunmType().equalsIgnoreCase("string")){
			result = cgfromcolumnMeta.getColumnName()+" character varying("+cgfromcolumnMeta.getColumnSize()+")"+" ";
		}else if(cgfromcolumnMeta.getColunmType().equalsIgnoreCase("date")){
			result = cgfromcolumnMeta.getColumnName()+" datetime"+" ";
		}else if(cgfromcolumnMeta.getColunmType().equalsIgnoreCase("int")){
			result = cgfromcolumnMeta.getColumnName()+" int("+cgfromcolumnMeta.getColumnSize()+")"+" ";
		}else if(cgfromcolumnMeta.getColunmType().equalsIgnoreCase("double")){
			result = cgfromcolumnMeta.getColumnName()+" numeric("+cgfromcolumnMeta.getColumnSize()+","+cgfromcolumnMeta.getDecimalDigits()+")"+" ";
		}
		return result;
	}

	
	@Override
	public String getCommentSql(ColumnMeta columnMeta) {
		return "COMMENT ON COLUMN "+columnMeta.getTableName()+"."+columnMeta.getColumnName()+" IS '" +columnMeta.getComment()+"'";
	}


	@Override
	public String dropIndexs(String indexName, String tableName) {
		return  "DROP INDEX " + indexName;
	}


	@Override
	public String countIndex(String indexName, String tableName) {
		return "SELECT count(*) FROM pg_indexes WHERE indexname = '"+indexName+"' and tablename = '"+tableName+"'";
	}


}
